﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.IO;
using System.Data;

namespace SqlServerDataTableToExcel.BLL
{
    public class LocalDs
    {

        private static readonly string LocalDSName = "SETTINGS.db";

        private static string FileName = Directory.GetCurrentDirectory() + "\\" + LocalDSName;
        /// <summary>
        /// 初始化配置数据库
        /// </summary>
        public static void Init()
        {
            ///判断数据库是否存在
           // string FileName = Directory.GetCurrentDirectory() + "\\" + LocalDSName;
            if (!File.Exists(FileName))
            {
                ///创建库
                DBhelper.SQLiteHelper.createNewDatabase(FileName);
                ///创建表
                string Sql = @"CREATE TABLE DATALIST 
(guid TEXT PRIMARY KEY NOT NULL,
  NAME TEXT NOT NULL,
  Type TEXT NOT NULL,
  IpAddress TEXT NOT NULL,
  DataSource TEXT NOT NULL,
  Ds_Name TEXT NOT NULL,
  Ds_Pwd TEXT NOT NULL,
  FILENAME TEXT NOT NULL,
  CREATE_DATA TEXT NOT NULL); ";
                DBhelper.SQLiteHelper.createTable(FileName, Sql);
            }
        }


        /// <summary>
        /// 保存在本地数据库
        /// </summary>
        /// <param name="itemMsg"></param>
        public static void Save(Model.Item_Model itemMsg)
        {
            string guid = itemMsg.guid;

            string Sql = "";

            DBhelper.SQLite MySql = DBhelper.SQLiteHelper.GetTrans(FileName);
            try
            {
                //删除数据
                Sql = "DELETE FROM  DATALIST WHERE guid = '{0}' ";
                Sql = string.Format(Sql, guid);

                DBhelper.SQLiteHelper.GetTransUpdate(MySql, Sql);

                //插入数据
                Sql = "";
                Sql = @" INSERT INTO DATALIST(    guid,
  NAME ,
  Type ,
  IpAddress  ,
  DataSource  ,
  Ds_Name,
  Ds_Pwd  ,
  FILENAME ,
   CREATE_DATA ) VALUES('" + itemMsg.guid +
       @"','" + itemMsg.Name +
       @"','" + itemMsg.Type +
       @"','" + itemMsg.IpAddress +
       @"','" + itemMsg.DataSource +
      @"','" + itemMsg.Ds_Name +
      @"','" + AppConfig.DESSymmetric.Encrypt(itemMsg.Ds_Pwd) +
      @"','" + itemMsg.FileName +
      @"','" + DateTime.Now.ToString("yyyyMMddHHmmss") + "')";

                DBhelper.SQLiteHelper.GetTransUpdate(MySql, Sql);
                DBhelper.SQLiteHelper.GetTransCommit(MySql);
            }
            catch (Exception ex)
            {
                DBhelper.SQLiteHelper.GetTransRollback(MySql);
                throw ex;
            }

        }


        public static void Update(Model.Item_Model itemMsg)
        {
            DBhelper.SQLite MySql = DBhelper.SQLiteHelper.GetTrans(FileName);
            try
            {
                string Sql = " UPDATE DATALIST SET  FILENAME = '{0}' WHERE  guid = '{1}'";
                Sql = string.Format(Sql, itemMsg.FileName, itemMsg.guid);
                DBhelper.SQLiteHelper.GetTransUpdate(MySql, Sql);
                DBhelper.SQLiteHelper.GetTransCommit(MySql);
            }
            catch (Exception ex)
            {
                DBhelper.SQLiteHelper.GetTransRollback(MySql);
                throw ex;
            }
        }


        /// <summary>
        /// 删除本地数据
        /// </summary>
        /// <param name="guid"></param>
        public static void Del(Model.Item_Model itemMsg)
        {
            string Sql = "DELETE FROM  DATALIST WHERE guid = '{0}' ";
            Sql = string.Format(Sql, itemMsg.guid);
            DBhelper.SQLiteHelper.ExecuteSQL(FileName, new List<string> { Sql });
        }


        /// <summary>
        /// 查询本地数据的配置
        /// </summary>
        /// <returns></returns>
        public static List<Model.Item_Model> Search()
        {
            List<Model.Item_Model> rtn = new List<Model.Item_Model>();


            string Sql = "SELECT * FROM DATALIST ORDER BY CREATE_DATA ASC ";
            DataTable dt = DBhelper.SQLiteHelper.Query(FileName, Sql);

            foreach (DataRow dr in dt.Rows)
            {
                Model.Item_Model item = new Model.Item_Model();
                item.guid = dr["guid"].ToString();
                item.Name = dr["Name"].ToString();
                item.Type = dr["Type"].ToString();
                item.IpAddress = dr["IpAddress"].ToString();
                item.DataSource = dr["DataSource"].ToString();
                item.Ds_Name = dr["Ds_Name"].ToString();
                item.Ds_Pwd = AppConfig.DESSymmetric.Decrypt(dr["Ds_Pwd"].ToString());
                item.FileName = dr["FileName"].ToString();

                rtn.Add(item);
            }


            return rtn;
        }

    }


}
